package com.babysun.commons.dbutil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DB implements Serializable{
	
	private static final long serialVersionUID = 1L;

	/** 数据源 */
	private static ComboPooledDataSource ds ;
	
	private static Connection connect ;
	
	public DB(ComboPooledDataSource c3p0ds) {
		ds = c3p0ds ;
	}
	
	public static Connection getConnect() throws SQLException {
		if(null == connect || connect.isClosed()) {
			connect = ds.getConnection() ; 
		}
		return connect ;
	}
	
	/**
	 * 修改数据
	 * @param sql
	 * @param val
	 * @return 修改记录数
	 * @throws Exception
	 */
	public static int update(String sql,Object[] val) throws Exception {
		PreparedStatement pstmt = getConnect().prepareStatement(sql) ;
		setParams(pstmt, val) ;
		return pstmt.executeUpdate() ;
	}
	
	/**
	 * 插入数据
	 * @param sql
	 * @param val
	 * @return true/false
	 * @throws Exception
	 */
	public static boolean insert(String sql,Object[] val) throws Exception {
		PreparedStatement pstmt = null ;
		try {
			pstmt = getConnect().prepareStatement(sql) ;
			
			setParams(pstmt, val) ;
			
			boolean flag = pstmt.execute() ;
			
			pstmt.close() ;
			
			return  flag ;
		} finally {
			if(null != pstmt && !pstmt.isClosed()) {
				pstmt.clearParameters() ;
				pstmt.close() ;
			}
			if(null != connect || connect.isClosed()) {
				connect.close() ;
			}
		}
	}
	
	/**
	 * 查询多条记录
	 * @param sql 查询语句
	 * @param queryParams 查询条件参数
	 * @return 返回List集合
	 * @throws Exception
	 */
	public static List<DBBean> query(String sql,Object[] queryParams) throws Exception {
		ArrayList<DBBean> ls = new ArrayList<DBBean>() ;
		
		PreparedStatement pstmt = getConnect().prepareStatement(sql) ;
		setParams(pstmt, queryParams) ;
		
		ResultSet rs = pstmt.executeQuery() ;
		while(rs.next()) {
			DBBean bean = new DBBean() ;
			
			for(int i=1; i<=pstmt.getMetaData().getColumnCount(); i++) {
				String field = pstmt.getMetaData().getColumnName(i) ;
				System.out.println(field);
				Serializable seriVal = (Serializable) rs.getObject(i) ;
				bean.setbean(field, seriVal) ;
			}
			ls.add(bean) ;
		}
		return ls ;
	}
	
	/**
	 * 查询一条记录
	 * @param sql
	 * @param queryParams
	 * @return
	 * @throws Exception
	 */
	public static DBBean getBeanFirst(String sql,Object[] queryParams) throws Exception {
		List<DBBean> list = query(sql, queryParams) ;
		
		if(list.isEmpty()) return null;
		
		return list.get(0) ;
	}
	
	@SuppressWarnings("unchecked")
	protected static void setParams(PreparedStatement pstmt,Object[] queryParams) throws SQLException {
		if(null != queryParams && queryParams.length > 0) {
			for(int i=0; i<queryParams.length; i++) {
				Object qp = queryParams[i] ;
				if(qp != null && qp instanceof List) {
					List<Serializable> list = (List<Serializable>) qp ;
					for(Serializable seri : list) {
						pstmt.setObject(i+1, seri) ;
					}
				} else {
					pstmt.setObject(i+1, queryParams[i]) ;
				}
			}
		}
	}
	

}
